/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cz.muni.fi.pa036is.daoimplementation;

import cz.muni.fi.pa036is.daointerface.AdmissionDAO;
import cz.muni.fi.pa036is.daointerface.CommentDAO;
import cz.muni.fi.pa036is.daointerface.ComplaintDAO;
import cz.muni.fi.pa036is.daointerface.MatchDAO;
import cz.muni.fi.pa036is.daointerface.RoundDAO;
import cz.muni.fi.pa036is.daointerface.SeasonDAO;
import cz.muni.fi.pa036is.entity.Admission;
import cz.muni.fi.pa036is.entity.Comment;
import cz.muni.fi.pa036is.entity.Complaint;
import cz.muni.fi.pa036is.entity.Match;
import cz.muni.fi.pa036is.entity.Person;
import cz.muni.fi.pa036is.entity.Round;
import cz.muni.fi.pa036is.entity.Season;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.springframework.stereotype.Repository;

/**
 *
 * @author Kuba
 */
@Repository
public class SeasonDAOImpl implements SeasonDAO {

	private Connection con = null;
	private Statement st = null;
	private ResultSet rs = null;

	public SeasonDAOImpl() throws ClassNotFoundException {



		String url = "jdbc:postgresql://localhost:5432/PA036";
		String user = "postgres";
		String password = "test";

		try {
			Class.forName("org.postgresql.Driver");
			con = DriverManager.getConnection(url, user, password);
		} catch (SQLException ex) {
			Logger.getLogger(SeasonDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
		}
	}

	@Override
	public void addSeason(Season season) {
		try {

			String insert = "INSERT INTO season"
					+ "(name, start_date, end_date, admission_price, points_for_win, points_for_draw, points_for_lose, victory_condition) VALUES"
					+ "(?,?,?,?,?,?,?,?)";
			PreparedStatement preparedStatement = con.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);


			preparedStatement.setString(1, season.getName());
			preparedStatement.setDate(2, season.getStartDate());
			preparedStatement.setDate(3, season.getEndDate());
			preparedStatement.setBigDecimal(4, season.getAdmissionPrice());
			preparedStatement.setInt(5, season.getPointsForWin());
			preparedStatement.setInt(6, season.getPointsForDraw());
			preparedStatement.setInt(7, season.getPointsForLose());
			preparedStatement.setInt(8, season.getVictoryCondition());
			preparedStatement.executeUpdate();

			ResultSet rs = preparedStatement.getGeneratedKeys();
			rs.next();
			Long personId = rs.getLong(1);
			season.setId(personId);

		} catch (SQLException ex) {
			Logger.getLogger(SeasonDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
		}
	}

	@Override
	public void editSeason(Season season) {
		try {

			String update = "UPDATE season SET name= ?, start_date= ?, end_date= ?, admission_price= ?, points_for_win= ?, "
					+ "points_for_draw= ?, points_for_lose= ?, victory_condition= ? WHERE id= ?";

			PreparedStatement preparedStatement = con.prepareStatement(update);

			preparedStatement.setString(1, season.getName());
			preparedStatement.setDate(2, season.getStartDate());
			preparedStatement.setDate(3, season.getEndDate());
			preparedStatement.setBigDecimal(4, season.getAdmissionPrice());
			preparedStatement.setInt(5, season.getPointsForWin());
			preparedStatement.setInt(6, season.getPointsForDraw());
			preparedStatement.setInt(7, season.getPointsForLose());
			preparedStatement.setInt(8, season.getVictoryCondition());

			preparedStatement.setLong(9, season.getId());

			preparedStatement.executeUpdate();
		} catch (SQLException ex) {
			Logger.getLogger(SeasonDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
		}
	}

	@Override
	public void deleteSeason(Long id) {
		try {

			// remove admission dependencies
			AdmissionDAO admissionDAO = new AdmissionDAOImpl();
			for (Admission a : admissionDAO.findAdmissionsForSeason(id)) {
				admissionDAO.deleteAdmission(a);
			}

			// remove matches dependencies
			MatchDAO matchDAO = new MatchDAOImpl();
			List<Match> matches = matchDAO.getAllMatchesForSeason(id);
			for (Match m : matches) {

				ComplaintDAO complaintDAO = new ComplaintDAOImpl();
				for (Complaint c : complaintDAO.getAllComplaintsForMatch(m.getId())) {
					complaintDAO.deleteComplaint(c.getId());
				}

				RoundDAO roundDAO = new RoundDAOImpl();
				for (Round r : roundDAO.getAllRoundsForMatch(m.getId())) {
					roundDAO.deleteRound(r.getMatchId(), r.getSequenceNumber());
				}

				CommentDAO commentDAO = new CommentDAOImpl();
				for (Comment c : commentDAO.getAllCommentsForMatch(m.getId())) {
					commentDAO.deleteComment(c.getId());
				}

				matchDAO.deleteMatch(m.getId());
			}


			String delete = "DELETE FROM season WHERE id=?";

			PreparedStatement preparedStatement = con.prepareStatement(delete);

			preparedStatement.setLong(1, id);

			preparedStatement.executeUpdate();
		} catch (SQLException ex) {
			Logger.getLogger(SeasonDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
		} catch (ClassNotFoundException ex) {
			Logger.getLogger(SeasonDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
		}
	}

	@Override
	public Season findSeason(Long id) {
		Season season = new Season();
		try {

			System.err.println("Id:" + id);
			
			String find = "SELECT * FROM season WHERE id=?";

			PreparedStatement preparedStatement = con.prepareStatement(find);

			preparedStatement.setLong(1, id);

			rs = preparedStatement.executeQuery();

			if (rs.next()) {
				season.setId(rs.getLong(1));
				season.setName(rs.getString(2));
				season.setStartDate(rs.getDate(3));
				season.setEndDate(rs.getDate(4));
				season.setAdmissionPrice(rs.getBigDecimal(5));
				season.setPointsForWin(rs.getInt(6));
				season.setPointsForDraw(rs.getInt(7));
				season.setPointsForLose(rs.getInt(8));
				season.setVictoryCondition(rs.getInt(9));
			}
		} catch (SQLException ex) {
			Logger.getLogger(SeasonDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
		}
		return season;
	}

	@Override
	public List<Season> getAllSeasons() {
		List<Season> seasonList = new ArrayList<Season>();

		try {

			String getAll = "SELECT * FROM season";

			PreparedStatement preparedStatement = con.prepareStatement(getAll);

			rs = preparedStatement.executeQuery();

			while (rs.next()) {
				Season season = new Season();
				season.setId(rs.getLong(1));
				season.setName(rs.getString(2));
				season.setStartDate(rs.getDate(3));
				season.setEndDate(rs.getDate(4));
				season.setAdmissionPrice(rs.getBigDecimal(5));
				season.setPointsForWin(rs.getInt(6));
				season.setPointsForDraw(rs.getInt(7));
				season.setPointsForDraw(rs.getInt(8));
				season.setVictoryCondition(rs.getInt(9));
				seasonList.add(season);

			}
		} catch (SQLException ex) {
			Logger.getLogger(PersonDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
		}
		return seasonList;
	}

	@Override
	public List<Season> getSeasonsByPerson(Person person) {
		List<Season> seasons = new ArrayList<>();
		try {

			String find = "SELECT s.id, s.name, s.start_date, s.end_date, s.admission_price, s.points_for_win, s.points_for_draw, s.points_for_lose, s.victory_condition FROM season s, match m WHERE m.season_id = s.id AND (m.player1_id = ? OR m.player2_id = ?)";

			PreparedStatement preparedStatement = con.prepareStatement(find);

			preparedStatement.setLong(1, person.getId());
			preparedStatement.setLong(2, person.getId());

			rs = preparedStatement.executeQuery();

			while (rs.next()) {
				Season season = new Season();
				season.setId(rs.getLong(1));
				season.setName(rs.getString(2));
				season.setStartDate(rs.getDate(3));
				season.setEndDate(rs.getDate(4));
				season.setAdmissionPrice(rs.getBigDecimal(5));
				season.setPointsForWin(rs.getInt(6));
				season.setPointsForDraw(rs.getInt(7));
				season.setPointsForLose(rs.getInt(8));
				season.setVictoryCondition(rs.getInt(9));

				seasons.add(season);
			}

		} catch (SQLException ex) {
			Logger.getLogger(SeasonDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
		}
		return seasons;
	}
}
